using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;
using System.Data.SqlClient;
/// <summary>
/// Summary description for CategoryDAO
/// </summary>
public class CategoryDAO:DBAccess
{
    #region sql command
    string SQL_GET_ALL = "select * from dbo.categories";
    string SQL_INSERT = "insert into categories([name]) values(@name)";
    string SQL_UPDATE = "update categories set [name]=@name where category_id=@id";
    string SQL_DELETE = "delete from categories where [category_id]=@id";
    #endregion
    #region fields
    string message = "";
    #endregion
    #region properties
    public string Message
    {
        get { return message; }
    }
    #endregion
    #region constructors
    public CategoryDAO():base()
	{     
    }
    #endregion
    #region methods
    public IList<BookCategory> GetAll()
    {
        IList<BookCategory> list = new List<BookCategory>();
        SqlCommand command = new SqlCommand(SQL_GET_ALL,conn);
        if (Connect())
        {
            SqlDataReader dr=command.ExecuteReader();
            while (dr.Read())
            {
                BookCategory cate = new BookCategory();
                cate.CategoryID = dr.GetInt32(0);
                cate.CategoryName = dr.GetString(1);
                list.Add(cate);
            }
            Disconnect();
        }
        return list;
    }
    public int Insert(BookCategory cate)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_INSERT,conn);
            command.Parameters.AddWithValue("@name", cate.CategoryName);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                command.CommandText = "select @@IDENTITY from categories";
                cate.CategoryID = int.Parse(command.ExecuteScalar().ToString());
                Disconnect();
            }
        }
        catch (Exception ex)
        {
            message = ex.Message;
        }
        return effectedRows;
    }
    public int Update(BookCategory cate)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_UPDATE,conn);
            command.Parameters.AddWithValue("@name", cate.CategoryName);
            command.Parameters.AddWithValue("@id", cate.CategoryID);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (Exception ex)
        {
            message = ex.Message;
        }
        return effectedRows;
    }
    public int Delete(BookCategory cate)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_DELETE,conn);
            command.Parameters.AddWithValue("@id", cate.CategoryID);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (Exception ex)
        {
            message = ex.Message;
        }
        return effectedRows;
    }
    #endregion
}
